{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: student@sparkifydb'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://student:student@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>songplay_id</th>\n",
       "        <th>start_time</th>\n",
       "        <th>user_id</th>\n",
       "        <th>level</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>song_id</th>\n",
       "        <th>session_id</th>\n",
       "        <th>location</th>\n",
       "        <th>user_agent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2018-11-09 00:06:17.796000</td>\n",
       "        <td>42</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>275</td>\n",
       "        <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>2018-11-09 00:09:46.796000</td>\n",
       "        <td>42</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>275</td>\n",
       "        <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>2018-11-09 00:12:27.796000</td>\n",
       "        <td>42</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>275</td>\n",
       "        <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>2018-11-09 00:14:52.796000</td>\n",
       "        <td>42</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>275</td>\n",
       "        <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2018-11-09 00:17:44.796000</td>\n",
       "        <td>42</td>\n",
       "        <td>paid</td>\n",
       "        <td>None</td>\n",
       "        <td>None</td>\n",
       "        <td>275</td>\n",
       "        <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "        <td>&quot;Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36&quot;</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, datetime.datetime(2018, 11, 9, 0, 6, 17, 796000), 42, 'paid', None, None, 275, 'New York-Newark-Jersey City, NY-NJ-PA', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " (2, datetime.datetime(2018, 11, 9, 0, 9, 46, 796000), 42, 'paid', None, None, 275, 'New York-Newark-Jersey City, NY-NJ-PA', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " (3, datetime.datetime(2018, 11, 9, 0, 12, 27, 796000), 42, 'paid', None, None, 275, 'New York-Newark-Jersey City, NY-NJ-PA', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " (4, datetime.datetime(2018, 11, 9, 0, 14, 52, 796000), 42, 'paid', None, None, 275, 'New York-Newark-Jersey City, NY-NJ-PA', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"'),\n",
       " (5, datetime.datetime(2018, 11, 9, 0, 17, 44, 796000), 42, 'paid', None, None, 275, 'New York-Newark-Jersey City, NY-NJ-PA', '\"Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36\"')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songplays LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>user_id</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "        <th>gender</th>\n",
       "        <th>level</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>88</td>\n",
       "        <td>Mohammad</td>\n",
       "        <td>Rodriguez</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>100</td>\n",
       "        <td>Adler</td>\n",
       "        <td>Barrera</td>\n",
       "        <td>M</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>25</td>\n",
       "        <td>Jayden</td>\n",
       "        <td>Graves</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>50</td>\n",
       "        <td>Ava</td>\n",
       "        <td>Robinson</td>\n",
       "        <td>F</td>\n",
       "        <td>free</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>42</td>\n",
       "        <td>Harper</td>\n",
       "        <td>Barrett</td>\n",
       "        <td>M</td>\n",
       "        <td>paid</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(88, 'Mohammad', 'Rodriguez', 'M', 'free'),\n",
       " (100, 'Adler', 'Barrera', 'M', 'free'),\n",
       " (25, 'Jayden', 'Graves', 'M', 'paid'),\n",
       " (50, 'Ava', 'Robinson', 'F', 'free'),\n",
       " (42, 'Harper', 'Barrett', 'M', 'paid')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>title</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>year</th>\n",
       "        <th>duration</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>SONWXQJ12A8C134D94</td>\n",
       "        <td>The Ballad Of Sleeping Beauty</td>\n",
       "        <td>ARNF6401187FB57032</td>\n",
       "        <td>1994</td>\n",
       "        <td>305.162</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SONWXQJ12A8C134D94', 'The Ballad Of Sleeping Beauty', 'ARNF6401187FB57032', 1994, 305.162)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>name</th>\n",
       "        <th>location</th>\n",
       "        <th>lattitude</th>\n",
       "        <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ARNF6401187FB57032</td>\n",
       "        <td>Sophie B. Hawkins</td>\n",
       "        <td>New York, NY [Manhattan]</td>\n",
       "        <td>40.79086</td>\n",
       "        <td>-73.96644</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('ARNF6401187FB57032', 'Sophie B. Hawkins', 'New York, NY [Manhattan]', Decimal('40.79086'), Decimal('-73.96644'))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://student:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>start_time</th>\n",
       "        <th>hour</th>\n",
       "        <th>day</th>\n",
       "        <th>week</th>\n",
       "        <th>month</th>\n",
       "        <th>year</th>\n",
       "        <th>weekday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-09 00:06:17.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>9</td>\n",
       "        <td>45</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-09 00:09:46.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>9</td>\n",
       "        <td>45</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-09 00:12:27.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>9</td>\n",
       "        <td>45</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-09 00:14:52.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>9</td>\n",
       "        <td>45</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>Friday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2018-11-09 00:17:44.796000</td>\n",
       "        <td>0</td>\n",
       "        <td>9</td>\n",
       "        <td>45</td>\n",
       "        <td>11</td>\n",
       "        <td>2018</td>\n",
       "        <td>Friday</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 11, 9, 0, 6, 17, 796000), 0, 9, 45, 11, 2018, 'Friday'),\n",
       " (datetime.datetime(2018, 11, 9, 0, 9, 46, 796000), 0, 9, 45, 11, 2018, 'Friday'),\n",
       " (datetime.datetime(2018, 11, 9, 0, 12, 27, 796000), 0, 9, 45, 11, 2018, 'Friday'),\n",
       " (datetime.datetime(2018, 11, 9, 0, 14, 52, 796000), 0, 9, 45, 11, 2018, 'Friday'),\n",
       " (datetime.datetime(2018, 11, 9, 0, 17, 44, 796000), 0, 9, 45, 11, 2018, 'Friday')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## REMEMBER: Restart this notebook to close connection to `sparkifydb`\n",
    "Each time you run the cells above, remember to restart this notebook to close the connection to your database. Otherwise, you won't be able to run your code in `create_tables.py`, `etl.py`, or `etl.ipynb` files since you can't make multiple connections to the same database (in this case, sparkifydb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %sql SELECT * FROM songs LEFT JOIN artists ON songs.artist_id=artists.artist_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
